﻿ if exists (select * from dbo.sysobjects where id = object_id(N'dbo.SourceColumn') and OBJECTPROPERTY(id, N'IsTable') = 1)
drop table dbo.SourceColumn

GO

CREATE TABLE dbo.SourceColumn
(
	ID int identity(1,1) CONSTRAINT PkMonitoredColumn_ID PRIMARY KEY CLUSTERED,
	SourceTableId int not null,
	OrdinalPosition int null,
	Name varchar(128) not null,
	FriendlyName varchar(128) null
	CONSTRAINT UnSourceColumn_SourceTableIdName UNIQUE (SourceTableId,Name)
)

GO

if exists (select * from dbo.sysobjects where id = object_id(N'trInsertRow') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop TRIGGER trInsertRow

GO

CREATE TRIGGER trInsertRow
   ON  dbo.SourceColumn
   INSTEAD OF INSERT
AS 
BEGIN

SET NOCOUNT ON

INSERT INTO dbo.SourceColumn 
	(SourceTableId, OrdinalPosition, Name, FriendlyName)
SELECT 
	SourceTableId, (SELECT ISNULL(MAX(OrdinalPosition)+1,1) FROM dbo.SourceColumn WHERE SourceTableId=INSERTED.SourceTableId), Name, FriendlyName
FROM 
	INSERTED

END
GO